/**
 * 执行sql查询语句
 * @param sql 查询语句
 * @returns {Promise<unknown>} 查询到的结果
 */
function executionSql(sql) {
    return new Promise((resolve, reject) => {
        // console.log("执行SQL查询->")
        // console.log(sql)
        ZOHO.CRM.API.coql({ select_query: sql }).then(function (data) {
            // console.log("执行SQL结果->")
            // console.log(data)
            resolve(data)
        }).catch(err => {
            console.log("查询SQL错误->")
            console.log(err)
            reject(reject)
        });
    })
}


//查询客户年月的之前插入的回款明细
async function getBeforeDetails(Name,FNumber,inputYear, inputMonth) {
    let pagelimit = 0;
    let listData = [];
    let flag = true;
    do {
        let sql = `select id,Name,field3 from CustomModule10 where ((((Name = '${Name}') and FNumber = '${FNumber}') and field4 = ${inputYear}) and field5 = ${inputMonth})  ORDER BY Created_Time DESC limit 200`
        if (pagelimit !== 0) {
            sql = `select id,Name,field3 from CustomModule10 where ((((Name = '${Name}') and FNumber = '${FNumber}') and field4 = ${inputYear}) and field5 = ${inputMonth})  ORDER BY Created_Time DESC limit ${pagelimit},200`
        }
        await executionSql(sql).then(data => {
            if (data.data !== undefined) {
                flag = data.info.more_records
                pagelimit += 200;
                listData.push(...data.data)
            } else {
                flag = false
            }
        })
    } while (flag)
    return listData
}

//根据客户年月查询欠款明细表-期初欠款金额
async function getBeginAmount(Name, inputYear, inputMonth) {
    let beginningAmount = 0;
    let listData = [];
    //根据客户年月查询欠款明细表-期初欠款金额
    //let sql = `select id,Name,field2,field3 from CustomModule11 where (((Name = '${Name}') and field4 = ${inputYear}) and field1 = ${inputMonth}) `
    //只根据客户名称匹配
    let sql = `select id,Name,field2,field3 from CustomModule11 where (Name = '${Name}') `
    await executionSql(sql).then(data => {
        if (data.status !== 204 || data.data !== undefined) {
            listData.push(...data.data)
        }
    })
    if (listData.length > 0) {
        if(listData[0].field3 !== null){
            beginningAmount = listData[0].field3
        }else if(listData[0].field2 !== null){
            beginningAmount = listData[0].field2
        }
    }
    console.log("期初欠款金额:" + beginningAmount)
    return beginningAmount
}

//获取开票金额
async function getInvoiceAmountData(Name, inputYear, inputMonth) {
    let invoiceAmount = 0;
    let pagelimit = 0;
    let listData = [];
    let flag = true;
    do {
        //客户名称、年、月、使用状态 匹配
        let sql = `select id,Name,field5 from CustomModule8 where ((((Name = '${Name}') and year = ${inputYear}) and month = ${inputMonth}) and UseStatus = '未使用')   limit 200`
        if (pagelimit !== 0) {
            sql = `select id,Name,field5 from CustomModule8 where ((((Name = '${Name}') and year = ${inputYear}) and month = ${inputMonth}) and UseStatus = '未使用') limit ${pagelimit},200`
        }
        //客户名称、客户代码、年、月、使用状态 匹配
        // let sql = `select id,Name,field5 from CustomModule8 where (((((Name = '${Name}') and field11 = '${FNumber}') and year = ${inputYear}) and month = ${inputMonth}) and UseStatus = '未使用')   limit 200`
        // if (pagelimit !== 0) {
        //     sql = `select id,Name,field5 from CustomModule8 where (((((Name = '${Name}') and field11 = '${FNumber}')  and year = ${inputYear}) and month = ${inputMonth}) and UseStatus = '未使用') limit ${pagelimit},200`
        // }
        await executionSql(sql).then(data => {
            if (data.data !== undefined) {
                flag = data.info.more_records
                pagelimit += 200;
                listData.push(...data.data)
            } else {
                flag = false;
            }
        })
    } while (flag)

    if (listData.length > 0) {
        listData.forEach(data => {
            invoiceAmount += data.field5
            data.UseStatus = '已使用'
        })
        console.log("tip:将客户匹配到的销售发票使用状态置为已使用: " + listData.length + "条")
        let tempData = []
        let count = 0
        for (let i = 1; i <= listData.length; i++) {
            tempData.push(listData[i - 1])
            if (i % 100 === 0 || (listData.length - i) < 100 && (i === listData.length)) {
                var config = {
                    Entity: "CustomModule8",
                    APIData: tempData,
                    Trigger: ["workflow", "blueprint", "approval"]
                }
                await ZOHO.CRM.API.updateRecord(config)
                .then(function (data) {
                    count++
                    console.log("第" + count + "次更新客户匹配到的销售发票使用状态成功！");
                })
                tempData = []
            }
        }
    }
    console.log("开票金额:" + (invoiceAmount).toFixed(2))
    return invoiceAmount
}

//获取客户按月汇总数据
async function getMonthData(Name,inputYear, inputMonth) {
    let listData = [];
    let sql = `select id,Name,includingTax_amount_cashed,notincludingTax_amount_cashed,overdue_Amount,debtAmount from CustomModule9 where ((((customer_name = '${Name}') and year = ${inputYear}) and month = ${inputMonth}) and regulation = '客户按月') `
    await executionSql(sql).then(data => {
        if (data.status !== 204 || data.data !== undefined) {
            listData.push(...data.data)
        }
    })
    if(listData.length <= 0){
        console.log("tip:无客户按月汇总数据")
    }
    return listData
}

//获取年份按月汇总数据
async function getYearData(inputYear,inputMonth) {
    let listData = [];
    let sql = `select id,Name,includingTax_amount_cashed,notincludingTax_amount_cashed,overdue_Amount,debtAmount from CustomModule9 where (((year = ${inputYear}) and month = ${inputMonth}) and regulation = '年份按月') `
    await executionSql(sql).then(data => {
        if (data.status !== 204 || data.data !== undefined) {
            listData.push(...data.data)
        }
    })
    if(listData.length <= 0){
        console.log("tip:无年份按月汇总数据")
    }
    return listData
}

//年汇总-总欠款
async function getSumDebtAmount(inputYear){
    let debtAmount = 0;
    let pagelimit = 0;
    let listData = [];
    let flag = true;
    do {
        //let sql = `select id,Name,field2,field3 from CustomModule11 where (field4 = ${inputYear}) limit 200`
        let sql = `select id,Name,field2,field3 from CustomModule11 where (Name is not null) limit 200`
        if (pagelimit !== 0) {
            //sql = `select id,Name,field2,field3 from CustomModule11 where (field4 = ${inputYear}) limit ${pagelimit},200`
            sql = `select id,Name,field2,field3 from CustomModule11 where (Name is not null) limit ${pagelimit},200`
        }
        await executionSql(sql).then(data => {
            if (data.data !== undefined) {
                flag = data.info.more_records
                pagelimit += 200;
                listData.push(...data.data)
            } else {
                flag = false;
            }
        })
    } while (flag)

    if (listData.length > 0) {
        listData.forEach(data => {
            if(data.field3 === null){
                debtAmount += data.field2 //期初欠款
              }else{
                debtAmount += data.field3 //欠款金额
              }
        })
    }
    console.log("年份按月-欠款金额:" + (debtAmount).toFixed(2))
    return (debtAmount).toFixed(2)
}

//年汇总-总超期
async function getSumOverdueAmount(inputYear){
    let overdueAmount = 0;
    let pagelimit = 0;
    let listData = [];
    let flag = true;
    do {
         //let sql = `select id,Name,field from CustomModule11 where (field4 = ${inputYear}) limit 200`
         let sql = `select id,Name,field from CustomModule11 where (Name is not null) limit 200`
         if (pagelimit !== 0) {
             //sql = `select id,Name,field from CustomModule11 where (field4 = ${inputYear}) limit ${pagelimit},200`
             sql = `select id,Name,field from CustomModule11 where (Name is not null) limit ${pagelimit},200`
         }
        await executionSql(sql).then(data => {
            if (data.data !== undefined) {
                flag = data.info.more_records
                pagelimit += 200;
                listData.push(...data.data)
            } else {
                flag = false;
            }
        })
    } while (flag)

    if (listData.length > 0) {
        listData.forEach(data => {
            if(data.field !== null){
                overdueAmount += data.field //超期金额
              }
        })
    }
    console.log("年份按月-超期金额:" + (overdueAmount).toFixed(2))
    return (overdueAmount).toFixed(2)
}


//根据ID获取导入数据
async function getImportData(ID) {
    let listData = [];
    let sql = `select id,Name,ifpaymentCollection from CustomModule12 where (id = ${ID}) `
    await executionSql(sql).then(data => {
        if (data.status !== 204 || data.data !== undefined) {
            listData.push(...data.data)
        }
    })
    return listData
}

//根据ID获取回款记录明细
async function getPaybackDetail(ID) {
    let listData = [];
    let sql = `select id,field2 from CustomModule10 where (id = ${ID}) `
    await executionSql(sql).then(data => {
        if (data.status !== 204 || data.data !== undefined) {
            listData.push(...data.data)
        }
    })
    return listData
}

 //获取客户当前账期
 async function getPaymentDays(Name,FNumber){
    let listData = [];
    //let sql = `select id,Name,PaymentDays from CustomModule13 where (Name = '${Name}')`/根据客户名称查询
    let sql = `select id,Name,PaymentDays from CustomModule13 where (FNumber = '${FNumber}')` //根据客户代码查询
    await executionSql(sql).then(data => {
        if (data.status !== 204 || data.data !== undefined) {
            listData.push(...data.data)
        }
    })
    return listData[0].PaymentDays
 }

 //获取账期内销售发票开票金额
 async function getbeforeMonthsData(Name,FNumber,beforeMonths){
    let invoiceAmount = 0
    let pagelimit = 0;
    let listData = [];
    let flag = true;
    do{
        //客户名称、年月 匹配
        let sql = `select id,Name,field5 from CustomModule8 where ((Name = '${Name}') and ym in (${beforeMonths})) limit 200`
        if (pagelimit !== 0) {
            sql = `select id,Name,field5 from CustomModule8 where ((Name = '${Name}') and ym in (${beforeMonths})) limit ${pagelimit},200`
        }
        //客户名称、客户代码、年月 匹配
        // let sql = `select id,Name,field5 from CustomModule8 where (((Name = '${Name}') and FNumber = '${FNumber}') and ym in (${beforeMonths})) limit 200`
        // if (pagelimit !== 0) {
        //     sql = `select id,Name,field5 from CustomModule8 where (((Name = '${Name}') and FNumber = '${FNumber}') and ym in (${beforeMonths})) limit ${pagelimit},200`
        // }
        await executionSql(sql).then(data => {
            if (data.data !== undefined) {
                flag = data.info.more_records
                pagelimit += 200;
                listData.push(...data.data)
            }else{
                flag = false;
            }
        })

    }while(flag)

    if(listData.length > 0){
        listData.forEach(data =>{
            invoiceAmount += data.field5
        })
    }
    return (invoiceAmount).toFixed(2)
 }

